package br.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import br.com.bean.MatriculaBean;

public class MatriculaDao {

	public void inserir(Connection conn, MatriculaBean value) throws SQLException {
		String sql = "INSERT INTO cad_matricula(id_aluno, nchamada, " +
				"tipo_matricula, id_ensino, id_serie, id_turno, id_turma, " +
				"ano_letivo, observacao, data_matricula)" +
				"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1, value.getIdaluno().getId());
		ps.setInt(2, value.getNchamada());
		ps.setString(3, value.getTipomatric());
		ps.setInt(4, value.getEnsino().getId());
		ps.setInt(5, value.getSerie().getId());
		ps.setInt(6, value.getTurno().getId());
		ps.setInt(7, value.getTurma().getId());
		ps.setInt(8, value.getAnolet());
		ps.setString(9, value.getObs());
		ps.setDate(10, new java.sql.Date(value.getDatamatric().getTime()));
		ps.execute();
		ps.close();

	}

	public void excluir(Connection conn, MatriculaBean value) throws Exception {

		String sql = "DELETE FROM cad_matricula WHERE id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ps.execute();
	}

	public void alterar(Connection conn, MatriculaBean value) throws Exception {

		String sql = "UPDATE cad_matricula SET id_aluno=?,  nchamada=?" +
				"tipo_matricula=?, id_ensino=?, id_serie=?, id_turno=?, id_turma=?," +
				"ano_letivo=?, observacao=?, data_matricula=? WHERE  id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getIdaluno().getId());
		ps.setInt(2, value.getNchamada());
		ps.setString(3, value.getTipomatric());
		ps.setInt(4, value.getEnsino().getId());
		ps.setInt(5, value.getSerie().getId());
		ps.setInt(6, value.getTurno().getId());
		ps.setInt(7, value.getTurma().getId());
		ps.setInt(8, value.getAnolet());
		ps.setString(9, value.getObs());
		ps.setDate(10, new java.sql.Date(value.getDatamatric().getTime()));
		ps.execute();
		ps.close();
	}

	public ArrayList<MatriculaBean> getLista(Connection conn, MatriculaBean value) throws Exception{

		String sql = " SELECT id_aluno, nchamada, tipo_matricula, id_ensino, " +
				"id_serie, id_turno, id_turma, ano_letivo, observacao," +
				" data_matricula FROM cad_matricula where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ResultSet rs = ps.executeQuery();
		ArrayList<MatriculaBean> al = new ArrayList<MatriculaBean>();
		while(rs.next()){
			MatriculaBean a = new MatriculaBean();
			a.getIdaluno().setId(rs.getInt("id_aluno"));
			a.setNchamada(rs.getInt("nchamada"));
			a.setTipomatric(rs.getString("tipo_matricula"));
			a.getEnsino().setId(rs.getInt("id_ensino"));
			a.getSerie().setId(rs.getInt("id_serie"));
			a.getTurno().setId(rs.getInt("id_turno"));
			a.getTurma().setId(rs.getInt("id_turma"));
			a.setAnolet(rs.getInt("ano_letivo"));
			a.setObs(rs.getString("observacao"));
			a.setDatamatric(rs.getDate("data_matricula"));
			al.add(a);
		}
		return al;
	}

	public ArrayList<MatriculaBean> getLista(Connection conn) throws Exception{

		String sql = "SELECT id_aluno, nchamada, tipo_matricula, id_ensino," +
				" id_serie, id_turno, id_turma, ano_letivo, observacao, " +
				"data_matricula FROM cad_matricula";

		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		ArrayList<MatriculaBean> al = new ArrayList<MatriculaBean>();
		while(rs.next()){
			MatriculaBean a = new MatriculaBean();
			a.getIdaluno().setId(rs.getInt("id_aluno"));
			a.setNchamada(rs.getInt("nchamada"));
			a.setTipomatric(rs.getString("tipo_matricula"));
			a.getEnsino().setId(rs.getInt("id_ensino"));
			a.getSerie().setId(rs.getInt("id_serie"));
			a.getTurno().setId(rs.getInt("id_turno"));
			a.getTurma().setId(rs.getInt("id_turma"));
			a.setAnolet(rs.getInt("ano_letivo"));
			a.setObs(rs.getString("observacao"));
			a.setDatamatric(rs.getDate("data_matricula"));
			al.add(a);
		}
		return al;
	}
}